# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import os
import json
from novel.items import TypeItem
from novel.items import NovelItem
from novel.items import ChapItem
from novel.items import ContentItem

from novel.items import ComicBookItem
from novel.items import ComicChapterItem
from novel.items import ComicContentItem

# class NovelPipeline(object):
#     def __init__(self):
#         pass
#
#     def process_item(self, item, spider):
#         print(item)
#         with open(item['filename'], 'w', encoding='utf-8') as f:
#             f.write(item['content'])
#
#         return item
#
#     def close_spider(self, spider):
#         """
#         爬虫运行结束之后关闭数据库
#         """
#         pass


# class CrawlNovelPipeline(object):
#     def __init__(self):
#         self.file = open('novel.json', "w", encoding='utf-8')
#
#     def process_item(self, item, spider):
#         print(item)
#         data = json.dumps(dict(item),  ensure_ascii=False) + ',\n'
#         self.file.write(data)
#         return item
#
#     def close_splide(self):
#         self.file.close()
import pymysql


class SqlNovelPipeline(object):
    def __init__(self):
        # 打开数据库连接
        self.conn = pymysql.connect(host='localhost', user='root', password='123456', database='novel', charset='utf8')
        # 使用cursor方法来创建一个游标对象
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        if isinstance(item, TypeItem):
            print(item)
            type_text = item['type_text']
            try:
                verify_sql = 'select * from type where type_text ="'+type_text+'"'
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into type(type_text) values(%s)'
                    self.cursor.execute(sql, [type_text])
                    # 提交事务
                    self.conn.commit()
            except Exception as e:
                print(e)

        elif isinstance(item, NovelItem):
            print(item)
            type_text = item['type_text']
            try:
                # 获得type_id
                get_type_id_sql = 'select type_id from type where type_text="'+type_text+'"'
                self.cursor.execute(get_type_id_sql)
                type_id = self.cursor.fetchone()
                # 验证小说是否已经插入
                verify_sql = 'select * from novel where novel_title ="' + item['novel_title'] + '"'
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into novel(novel_title, novel_author, novel_desc, cover, type_id)' \
                          ' values(%s,%s,%s,%s,%s)'
                    self.cursor.execute(sql, [item['novel_title'], item['novel_author'], item['novel_desc']
                                              , item['cover'], type_id])
                    # 提交事务
                    self.conn.commit()
            except Exception as e:
                print(e)

        elif isinstance(item, ChapItem):
            print(item)
            try:
                # 获取书籍id
                get_novel_id_sql = 'select novel_id from novel where novel_title="'+item['novel_title']+'"'
                self.cursor.execute(get_novel_id_sql)
                novel_id = self.cursor.fetchone()  # 返回元组
                # 验证小说是否已经插入
                verify_sql = 'select * from chapter where chap_title ="' \
                             + item['chap_title'] + '" and novel_id=' + str(novel_id[0])
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into chapter(chap_title, novel_id, ctime) values(%s,%s,%s)'
                    self.cursor.execute(sql, [item['chap_title'], novel_id, item['ctime']])
                    # 提交事务
                    self.conn.commit()
            except Exception as e:
                print(e)

        elif isinstance(item, ContentItem):
            print(item)
            try:
                # 获取书籍id
                get_chap_id_sql = 'select chap_id from chapter where chap_title="' + item['chap_title'] + '"'
                self.cursor.execute(get_chap_id_sql)
                chap_id = self.cursor.fetchone()  # 返回元组
                # 验证小说是否已经插入
                verify_sql = 'select * from content where chap_id =' + str(chap_id[0])
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into content(content, chap_id) values(%s,%s)'
                    self.cursor.execute(sql, [item['content'], chap_id])
                    # 提交事务
                    self.conn.commit()
            except Exception as e:
                print(e)

    def close_spider(self, spider):
        # 关闭相应的连接
        self.cursor.close()
        self.conn.close()

# --------- 漫画 管道 --------


class ComicPipeline(object):
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='123456', database='novel', charset='utf8')
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        if isinstance(item, ComicBookItem):
            print(item)
            try:
                verify_sql = 'select * from comic_book where comic_title ="' + item['comic_title'] + '"'
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into comic_book' \
                          '(comic_id, comic_title, comic_author, comic_state, comic_type, comic_intro, comic_cover) ' \
                          'values(%s, %s,%s,%s,%s,%s,%s)'
                    self.cursor.execute(sql, [item['comic_id'], item['comic_title'], item['comic_author'], item['comic_state'],
                                              item['comic_type'], item['comic_intro'], item['comic_cover']])
                    self.conn.commit()
            except Exception as e:
                print(e)
        elif isinstance(item, ComicChapterItem):
            print(item)
            try:
                # 得到漫画书id
                get_comic_id_sql = 'select comic_id from comic_book where comic_title ="' + item['comic_title'] + '"'
                self.cursor.execute(get_comic_id_sql)
                comic_id = self.cursor.fetchone()
                verify_sql = 'select * from comic_chapter ' \
                             'where comic_chap_title ="' + item['comic_chap_title'] + '" and comic_id = "' + str(comic_id[0]) + '"'
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into comic_chapter' \
                          '(comic_chap_title, comic_id) ' \
                          'values(%s,%s)'
                    self.cursor.execute(sql, [item['comic_chap_title'], comic_id])
                    self.conn.commit()
            except Exception as e:
                print(e)
        elif isinstance(item, ComicContentItem):
            print(item)
            try:
                # 得到漫画章节id
                get_comic_chap_id_sql = 'select comic_chap_id from comic_chapter' \
                                        ' where comic_chap_title ="' + item['comic_chap_title'] + '"' \
                                          'and comic_id = "' + item['comic_id'] + '"'
                self.cursor.execute(get_comic_chap_id_sql)
                comic_chap_id = self.cursor.fetchone()
                verify_sql = 'select * from comic_content ' \
                             'where  comic_chap_id = "' + str(comic_chap_id[0]) + '" and' \
                                ' comic_con_src = "' + item['comic_con_src'] + '"'
                row = self.cursor.execute(verify_sql)
                if row == 0:
                    sql = 'insert into comic_content' \
                          '(comic_con_src, comic_con_page, comic_chap_id) ' \
                          'values(%s,%s,%s)'
                    self.cursor.execute(sql, [item['comic_con_src'], item['comic_con_page'],  comic_chap_id])
                    self.conn.commit()
            except Exception as e:
                print(e)

    def close_spider(self, spider):
        # 关闭相应的连接
        self.cursor.close()
        self.conn.close()


from novel.dbHelp import DBHelp
from novel.items import CWMTypeItem
from novel.items import CWMNovelItem
from novel.items import CWMRollItem
from novel.items import CWMChapterItem
from novel.items import CWMContentItem

# 刺猬猫小说
class CWMPipeline(object):
    def __init__(self):
        self.dbHelp = DBHelp()
        self.dbHelp.open('localhost', 'root', '123456', 'novel', 'utf8')

    def process_item(self, item, spider):
        if isinstance(item, CWMTypeItem):
            print(item)
            verify_sql = 'select * from cwm_type where type_title = "{}"'.format(item['type_title'])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into cwm_type(type_title) values("{}")'.format(item['type_title'])
                self.dbHelp.insert(sql)
        elif isinstance(item, CWMNovelItem):
            print(item)
            get_type_id_sql = 'select type_id from cwm_type where type_title = "{}"'.format(item['type_title'])
            [row, type_id] = self.dbHelp.select(get_type_id_sql)
            verify_sql = 'select * from cwm_novel where novel_title = "{}"'.format(item['novel_title'])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into ' \
                      'cwm_novel(novel_title, novel_author, novel_state, novel_tag, novel_intro, cover, type_id) ' \
                      'values("{}","{}","{}","{}","{}","{}",{})'\
                      .format(item['novel_title'], item['novel_author'], item['novel_state'],
                              item['novel_tag'], item['novel_intro'], item['cover'], type_id[0])
                self.dbHelp.insert(sql)
        elif isinstance(item, CWMRollItem):
            print(item)
            get_novel_id_sql = 'select novel_id from cwm_novel where novel_title = "{}"'.format(item['novel_title'])
            [row, novel_id] = self.dbHelp.select(get_novel_id_sql)
            verify_sql = 'select * from cwm_roll where  roll_title = "{}" and novel_id = {}'.format(item['roll_title'], novel_id[0])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into ' \
                      'cwm_roll(roll_title, novel_id) ' \
                      'values("{}",{})' \
                    .format(item['roll_title'],  novel_id[0])
                self.dbHelp.insert(sql)
        elif isinstance(item, CWMChapterItem):
            print(item)
            # 光靠roll_title不能准确找到id
            get_novel_id_sql = 'select novel_id from cwm_novel where novel_title = "{}"'.format(item['novel_title'])
            [row1, novel_id] = self.dbHelp.select(get_novel_id_sql)
            get_roll_id_sql = 'select roll_id from cwm_roll where roll_title = "{}" and novel_id = {}'.format(item['roll_title'], novel_id[0])
            [row2, roll_id] = self.dbHelp.select(get_roll_id_sql)
            verify_sql = 'select * from cwm_chapter where  chap_title = "{}" and roll_id = {}'.format(item['chap_title'], roll_id[0])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into ' \
                      'cwm_chapter(chap_title, roll_id, ctime) ' \
                      'values("{}",{},"{}")' \
                    .format(item['chap_title'], roll_id[0], item['ctime'])
                self.dbHelp.insert(sql)
        elif isinstance(item, CWMContentItem):
            print(item)
            get_novel_id_sql = 'select novel_id from cwm_novel where novel_title = "{}"'.format(item['novel_title'])
            [row1, novel_id] = self.dbHelp.select(get_novel_id_sql)
            get_roll_id_sql = 'select roll_id from cwm_roll where roll_title = "{}" and novel_id = {}'.format(
                item['roll_title'], novel_id[0])
            [row2, roll_id] = self.dbHelp.select(get_roll_id_sql)
            get_chap_id_sql = 'select chap_id from cwm_chapter where chap_title = "{}" and roll_id = {}'.format(item['chap_title'], roll_id[0])
            [row, chap_id] = self.dbHelp.select(get_chap_id_sql)
            verify_sql = 'select * from cwm_content where  chap_id = {}'.format(chap_id[0])
            row = self.dbHelp.select(verify_sql)[0]
            if row == 0:
                sql = 'insert into ' \
                      'cwm_content(content_text, chap_id) ' \
                      'values("{}",{})' \
                    .format(item['content_text'], chap_id[0])
                self.dbHelp.insert(sql)

    def close_spider(self, spider):
        self.dbHelp.close()